package core;

//import java.io.File;
//import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;

import dataBase.DbConnection;

import xml.MyDom4j;

public class DbInfo {

	public static List<String> GetTableNames(Connection Conn)
			throws DocumentException, Exception {
		List<String> tableNames = new ArrayList<String>();
		DatabaseMetaData dbmd = Conn.getMetaData();
		ResultSet rs = dbmd.getTables(null, null, null,
				new String[] { "TABLE" });
		while (rs.next()) {
			tableNames.add(rs.getString("TABLE_NAME"));
		}
		return tableNames;
	}

	private static List<String> GetPrimeKey(Connection Conn, String tableName)
			throws SQLException, Exception {
		try {
			List<String> PrimeKeylist = new ArrayList<String>();
			DatabaseMetaData dbmd = Conn.getMetaData();
			ResultSet rs = dbmd.getPrimaryKeys(null, null,
					tableName.toUpperCase());
			while (rs.next()) {

				PrimeKeylist.add(rs.getString("COLUMN_NAME"));
			}
			return PrimeKeylist;
		} catch (Exception ex) {
			throw ex;
		}
	}

	private static List<List<String>> GetTableStruct(Connection Conn,
			String tableName) throws SQLException, Exception {
		List<List<String>> tableStruct = new ArrayList<List<String>>();
		List<String> columnMetaName = new ArrayList<String>();
		DatabaseMetaData dbmd = Conn.getMetaData();
		ResultSet rs = dbmd.getColumns(null, null, tableName.toUpperCase(),
				null);
		ResultSetMetaData rsmd = rs.getMetaData();
		int cols = rsmd.getColumnCount();
		for (int i = 1; i <= cols; i++) {
			// System.out.print(rsmd.getColumnName(i) + "\t");
			columnMetaName.add(rsmd.getColumnName(i));
		}
		tableStruct.add(columnMetaName);

		while (rs.next()) {
			List<String> ColumnMetaData = new ArrayList<String>();
			for (int i = 1; i <= cols; i++) {

				ColumnMetaData.add(rs.getString(i));
			}
			tableStruct.add(ColumnMetaData);
		}
		return tableStruct;
	}

	private static Document CreateDocumentRoot(String dbType) {
		Document doc = MyDom4j.CreatDocumentRoot("DATABASE");
		MyDom4j.AddRootAttribute(doc, "DBTYPE", dbType);
		// Element root = MyDom4j.GetRootElement(doc);
		return doc;
	}

	private static Document AddTable(Document doc, String tbName,
			List<List<String>> list) {
		Element root = MyDom4j.GetRootElement(doc);
		Element table = root.addElement("TABLE");
		List<String> metaNames = list.get(0);
		int columnNum = metaNames.size();
		// table.addAttribute(TABLENAME, tableName);
		Iterator<List<String>> listIt = list.iterator();
		if (listIt.hasNext()) {
			listIt.next();
		}
		while (listIt.hasNext()) {
			List<String> metaData = listIt.next();
			Element field = table.addElement("FIELD");
			// System.out.println(metaData);
			for (int i = 0; i < columnNum; i++) {
				Element element = field.addElement(metaNames.get(i));
				if (metaData.get(i) != null) {
					element.addText(metaData.get(i));
				}
			}
		}
		table.addAttribute("TABLE_NAME", tbName);
		return doc;
	}

	private static Document AddPrimeKey(Document doc, String tbName,
			Connection dbConnection) throws SQLException, Exception {
		Element root = MyDom4j.GetRootElement(doc);
		Element table = null;
		for (@SuppressWarnings("unchecked")
		Iterator<Element> i = root.elementIterator("TABLE"); i.hasNext();) {
			Element e = i.next();
			// System.out.println(e);
			if (e.attributeValue("TABLE_NAME").equalsIgnoreCase(tbName)) {
				table = e;
				break;
			}
		}
		try {
			List<String> primeKeyList = GetPrimeKey(dbConnection, tbName);
			Iterator<String> it = primeKeyList.iterator();
			while (it.hasNext()) {
				Element pKey = table.addElement("PRIMEKEY");
				pKey.addAttribute("PRIMEKEYNAME", "");
				pKey.addText(it.next());
				// System.out.println(pKey);
			}
			return doc;
		} catch (Exception ex) {
			throw ex;
		}
	}

	public static Document GetStruct(DbConnection dbCon, List<String> tbList)
			throws SQLException, Exception {
		if (tbList == null) {
			System.out
					.println("These Tables can't be Transferred Pleas Choose Tables Again");
			return null;
		}
		try {
			Document doc = CreateDocumentRoot(dbCon.GetCurrentDbType());
			for (int i = 0; i < tbList.size(); i++) {
				String tbName = tbList.get(i);
				List<List<String>> list = GetTableStruct(dbCon.GetConnection(),
						tbName);
				doc = AddTable(doc, tbName, list);
				doc = AddPrimeKey(doc, tbName, dbCon.GetConnection());
			}
			doc = AddForeignKey(doc, dbCon, tbList);
			return doc;
		} catch (Exception ex) {
			throw ex;
		}

	}

	private static List<String> GetReadyTableList(List<String> tbList,
			List<List<String>> importKeysTableLists) {
		List<String> readyTalbeList = new ArrayList<String>();
		for (int i = 0; i < tbList.size(); i++) {
			if (importKeysTableLists.get(i).size() == 0) {
				readyTalbeList.add(tbList.get(i));
			}
		}

		return readyTalbeList;
	}

	private static List<String> GetNotReadyTableList(List<String> tbList,
			List<List<String>> importKeysTableLists) {
		List<String> notReadyTalbeList = new ArrayList<String>();
		for (int i = 0; i < tbList.size(); i++) {
			if (importKeysTableLists.get(i).size() != 0) {
				notReadyTalbeList.add(tbList.get(i));
			}
		}
		return notReadyTalbeList;
	}

	private static List<List<String>> GetImportKeysList(
			List<String> notReadyTalbeList, List<String> tbList,
			List<List<String>> importKeysTableLists) {

		List<List<String>> importKeysList = new ArrayList<List<String>>();
		for (int i = 0; i < notReadyTalbeList.size(); i++) {
			String notReadyTable = notReadyTalbeList.get(i);
			int tbIndex = tbList.indexOf(notReadyTable);
			List<String> importKeys = importKeysTableLists.get(tbIndex);
			importKeysList.add(importKeys);
		}
		return importKeysList;
	}

	private static List<String> ReSortTableList(List<String> readyTalbeList,
			List<String> notReadyTalbeList,
			List<List<String>> importKeysTableLists) {
		while (notReadyTalbeList.size() > 0) {
			for (int i = 0; i < notReadyTalbeList.size(); i++) {
				String notReadyTalbe = notReadyTalbeList.get(i);
				List<String> importKeys = importKeysTableLists.get(i);
				boolean canRemove = true;
				for (int j = 0; j < importKeys.size(); j++) {
					if (readyTalbeList.indexOf(importKeys.get(j)) == -1) {
						canRemove = false;
					}
				}
				if (canRemove) {
					readyTalbeList.add(notReadyTalbe);
					notReadyTalbeList.remove(notReadyTalbe);
				}
			}
		}

		return readyTalbeList;
	}

	public static List<String> JudgeTableList(List<String> tbList,
			DbConnection dbCon) throws SQLException {

		List<List<String>> importKeysTableLists = DbConnection
				.GetImportKeysTableLists(dbCon, tbList);
		// System.out.println(importKeysTableLists);
		List<String> notReadyTableList = GetNotReadyTableList(tbList,
				importKeysTableLists);
		if (notReadyTableList.size() == 0) {
			// System.out.println("NO ForeignKeys");
			return tbList;
		}
		for (int notReadyTableIndex = 0; notReadyTableIndex < notReadyTableList
				.size(); notReadyTableIndex++) {
			String notReadyTable = notReadyTableList.get(notReadyTableIndex);
			int tbIndex = tbList.indexOf(notReadyTable);
			List<String> readyTableList = importKeysTableLists.get(tbIndex);

			for (int readyTableIndex = 0; readyTableIndex < readyTableList
					.size(); readyTableIndex++) {
				String readyTable = readyTableList.get(readyTableIndex);
				if (tbList.indexOf(readyTable) == -1) {
					System.out.println("The Table " + readyTable
							+ " Is Not Exist In TalbeList");
					return null;
				}
			}
		}
		List<String> readyTalbeList = GetReadyTableList(tbList,
				importKeysTableLists);

		List<List<String>> importKeysList = GetImportKeysList(
				notReadyTableList, tbList, importKeysTableLists);

		return ReSortTableList(readyTalbeList, notReadyTableList,
				importKeysList);
	}

	public static List<List<String>> GetForeignKeyInfoList(DbConnection dbCon,
			List<String> notReadyTableList) throws SQLException {

		List<List<String>> foreignKeyInfoList = new ArrayList<List<String>>();
		DatabaseMetaData dbmd = dbCon.GetConnection().getMetaData();
		for (int i = 0; i < notReadyTableList.size(); i++) {
			ResultSet rs = dbmd.getImportedKeys(null, null,
					notReadyTableList.get(i));
			while (rs.next()) {
				List<String> list = new ArrayList<String>();
				String fkName = rs.getString("FK_NAME");
				String fkTableName = rs.getString("FKTABLE_NAME");
				String pkTableName = rs.getString("PKTABLE_NAME");
				String updateRule = rs.getString("UPDATE_RULE");
				String deleteRule = rs.getString("DELETE_RULE");
				String fkColumnName = rs.getString("FKCOLUMN_NAME");
				String pkColumnName = rs.getString("PKCOLUMN_NAME");
				String keySeq = rs.getString("KEY_SEQ");

				list.add(fkName);
				list.add(fkTableName);
				list.add(fkColumnName);
				list.add(pkTableName);
				list.add(pkColumnName);
				list.add(keySeq);
				list.add(updateRule);
				list.add(deleteRule);
				foreignKeyInfoList.add(list);
			}
			foreignKeyInfoList.add(null);
		}
		// System.out.println(foreignKeyInfoList);
		return foreignKeyInfoList;

	}

	public static Document AddForeignKey(Document doc, DbConnection dbCon,
			List<String> tbList) throws SQLException {

		List<List<String>> importKeysTableLists = DbConnection
				.GetImportKeysTableLists(dbCon, tbList);

		List<String> notReadyTableList = GetNotReadyTableList(tbList,
				importKeysTableLists);

		Element root = doc.getRootElement();

		List<List<String>> foreignKeyInfoList = GetForeignKeyInfoList(dbCon,
				notReadyTableList);

		int beginIndex = 0;
		while (beginIndex < foreignKeyInfoList.size()) {
			int endIndex;
			List<String> foreignKeyNames = new ArrayList<String>();
			for (endIndex = beginIndex; foreignKeyInfoList.get(endIndex) != null; endIndex++) {
				String fkName = foreignKeyInfoList.get(endIndex).get(0);
				if (foreignKeyNames.indexOf(fkName) == -1) {
					foreignKeyNames.add(fkName);
				}
			}

			for (int fkIndex = 0; fkIndex < foreignKeyNames.size(); fkIndex++) {
				String fkName = foreignKeyNames.get(fkIndex);

				int infoIndex = beginIndex;
				Element foreignKey = root.addElement("FOREIGNKEY");
				foreignKey.addAttribute("FOREIGNKEYNAME", fkName);
				boolean isFirstFound = true;

				while (infoIndex < endIndex) {
					if (foreignKeyInfoList.get(infoIndex).get(0)
							.equalsIgnoreCase(fkName)) {
						List<String> fkInfo = foreignKeyInfoList.get(infoIndex);

						if (isFirstFound) {
							String fkTableName = fkInfo.get(1);
							String pkTableName = fkInfo.get(3);
							String updateRule = fkInfo.get(6);
							String deleteRule = fkInfo.get(7);
							if (updateRule.equalsIgnoreCase("1")) {
								updateRule = "NO ACTION";
							}
							if (updateRule.equalsIgnoreCase("0")) {
								updateRule = "CASCADE";
							}
							if (deleteRule.equalsIgnoreCase("1")) {
								deleteRule = "NO ACTION";
							}
							if (deleteRule.equalsIgnoreCase("0")) {
								deleteRule = "CASCADE";
							}
							foreignKey.addAttribute("ForeignKeyTableName",
									fkTableName);
							foreignKey.addAttribute("PrimeKeyTableName",
									pkTableName);
							foreignKey.addAttribute("UpdateRule", updateRule);
							foreignKey.addAttribute("DeleteRule", deleteRule);
							isFirstFound = false;
						}

						String fkColumnName = fkInfo.get(2);
						String pkColumnName = fkInfo.get(4);
						String keySeq = fkInfo.get(5);

						Element foreignKeyColumnName = foreignKey
								.addElement("ForeignKeyColumnName");
						foreignKeyColumnName.setText(fkColumnName);
						foreignKeyColumnName.addAttribute("keySeq", keySeq);
						Element primeKeyColumnName = foreignKey
								.addElement("PrimeKeyColumnName");
						primeKeyColumnName.setText(pkColumnName);
						primeKeyColumnName.addAttribute("keySeq", keySeq);
					}
					infoIndex++;
				}
			}
			beginIndex = endIndex + 1;
		}

		return doc;
	}

	static void TestforForeignKey() throws DocumentException, Exception {

		List<String> tbList = new ArrayList<String>();
		tbList.add("SC3");
		tbList.add("SC2");
		tbList.add("SC");
		tbList.add("S");
		tbList.add("C");
		// System.out.println(tbList);
		DbConnection dbCon = new DbConnection("MSSQL_2", "sa", "123456");
		try {
			dbCon.Connection();
			tbList = JudgeTableList(tbList, dbCon);
			//
			// System.out.println(tbList);

			List<List<String>> importKeysTableLists = DbConnection
					.GetImportKeysTableLists(dbCon, tbList);

			List<String> notReadyTableList = GetNotReadyTableList(tbList,
					importKeysTableLists);

			List<List<String>> foreignKeyInfoList = GetForeignKeyInfoList(
					dbCon, notReadyTableList);
			for (int i = 0; i < foreignKeyInfoList.size(); i++) {
				System.out.println(foreignKeyInfoList.get(i));
			}

			int beginIndex = 0;
			while (beginIndex < foreignKeyInfoList.size()) {
				int endIndex;
				List<String> foreignKeyNames = new ArrayList<String>();
				for (endIndex = beginIndex; foreignKeyInfoList.get(endIndex) != null; endIndex++) {
					String fkName = foreignKeyInfoList.get(endIndex).get(0);
					if (foreignKeyNames.indexOf(fkName) == -1) {
						foreignKeyNames.add(fkName);
					}
				}
				System.out.println(foreignKeyNames);
				beginIndex = endIndex + 1;
			}

		} finally {
			// System.out.println("for test");
			dbCon.CloseConnection();
			// System.out.println();
		}
	}

	static void TestforGetStruct() throws DocumentException, Exception {
		List<String> tbList = new ArrayList<String>();
		tbList.add("SC3");
		tbList.add("SC2");
		tbList.add("SC");
		tbList.add("S");
		tbList.add("C");
		DbConnection dbCon = new DbConnection("MSSQL_2", "sa", "123456");
		try {
			dbCon.Connection();

			tbList = DbInfo.JudgeTableList(tbList, dbCon);

			// System.out.println(tbList);

			List<List<String>> importKeysTableLists = DbConnection
					.GetImportKeysTableLists(dbCon, tbList);
			List<String> notReadyTableList = GetNotReadyTableList(tbList,
					importKeysTableLists);
			List<List<String>> foreignKeyInfoList = GetForeignKeyInfoList(
					dbCon, notReadyTableList);
			for (int i = 0; i < foreignKeyInfoList.size(); i++) {
				System.out.println(foreignKeyInfoList.get(i));
			}

			Document doc = DbInfo.GetStruct(dbCon, tbList);

			MyDom4j.CreatXML(doc, "test", "TableStruct.xml");

		} finally {
			dbCon.CloseConnection();

		}
	}

	public static void main(String[] args) throws DocumentException, Exception {

		TestforGetStruct();
	}
}
